/*
 * Copyright (C) 2021 Huawei Device Co., Ltd.
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package io.requery.ohos.sqlite.demo.slice;

import io.requery.ohos.database.sqlite.SQLiteDatabase;
import io.requery.ohos.sqlite.demo.ResourceTable;
import io.requery.ohos.sqlite.demo.SqliteContract;
import io.requery.ohos.sqlite.demo.SqliteDbHelper;

import ohos.aafwk.ability.AbilitySlice;
import ohos.aafwk.content.Intent;
import ohos.agp.components.Button;
import ohos.agp.components.Component;
import ohos.data.rdb.ValuesBucket;
import ohos.data.resultset.ResultSet;
import ohos.hiviewdfx.HiLog;
import ohos.hiviewdfx.HiLogLabel;
import ohos.miscservices.timeutility.Time;

/**
 * ExtFeatureAbilitySlice
 */
public class ExtFeatureAbilitySlice extends AbilitySlice {
    private static final HiLogLabel LABEL = new HiLogLabel(HiLog.LOG_APP, 0x002A, "ExtFeatureAbilitySlice");
    private static final String TEXT_TYPE = " TEXT";
    private static final String INTEGER_TYPE = " INTEGER";
    private static final String GENERATED_ALWAYS_AS = " GENERATED ALWAYS AS";
    private static final String SQL_CREATE_ENTRIES =
            "CREATE TABLE " + "Generated" + " (" +
                    SqliteContract.SqlEntry.ID + " INTEGER PRIMARY KEY," +
                    SqliteContract.SqlEntry.COLUMN_NAME_EMPNAME + TEXT_TYPE + "," +
                    SqliteContract.SqlEntry.COLUMN_NAME_AGE + INTEGER_TYPE + "," +
                    SqliteContract.SqlEntry.COLUMN_NAME_GENERATED + TEXT_TYPE + GENERATED_ALWAYS_AS +
                    "(substr(\"Name\",0,5)) )";

    private SQLiteDatabase mDB;

    @Override
    public void onStart(Intent intent) {
        super.onStart(intent);
        super.setUIContent(ResourceTable.Layout_ability_ext);
        mDB = SqliteDbHelper.getInstance(this).getWritableDatabase();
        initTables();
        Button generatedBtn = (Button) findComponentById(ResourceTable.Id_generated);
        generatedBtn.setClickedListener(new Component.ClickedListener() {
            @Override
            public void onClick(Component component) {
                testGeneratedColumn();
            }
        });

        Button searchBtn = (Button) findComponentById(ResourceTable.Id_search);
        searchBtn.setClickedListener(new Component.ClickedListener() {
            @Override
            public void onClick(Component component) {
                testFTS5();
            }
        });

        Button indexBtn = (Button) findComponentById(ResourceTable.Id_index);
        indexBtn.setClickedListener(new Component.ClickedListener() {
            @Override
            public void onClick(Component component) {
                textIndex();
            }
        });
    }

    @Override
    public void onStop() {
        super.onStop();
        mDB.close();
    }

    private void initTables() {
        createGenerateTable();
        createIndexTable();
        createSearchTable();
    }

    private void createGenerateTable() {
        mDB.execSQL("DROP TABLE IF EXISTS Generated");
        mDB.execSQL(SQL_CREATE_ENTRIES);
        ValuesBucket values = new ValuesBucket();
        values.putString(SqliteContract.SqlEntry.COLUMN_NAME_EMPNAME, "XYZoiycjfjbj");
        values.putInteger(SqliteContract.SqlEntry.COLUMN_NAME_AGE, 25);
        mDB.insert("Generated", null, values);
        values = new ValuesBucket();
        values.putString(SqliteContract.SqlEntry.COLUMN_NAME_EMPNAME, "ABCYZas");
        values.putInteger(SqliteContract.SqlEntry.COLUMN_NAME_AGE, 28);
        mDB.insert("Generated", null, values);
        values = new ValuesBucket();
        values.putString(SqliteContract.SqlEntry.COLUMN_NAME_EMPNAME, "ijhjkl");
        values.putInteger(SqliteContract.SqlEntry.COLUMN_NAME_AGE, 65);
        mDB.insert("Generated", null, values);
    }

    private void createSearchTable() {
        mDB.execSQL("DROP TABLE IF EXISTS email");
        String statement = "CREATE VIRTUAL TABLE email USING fts5(sender, title, body)" ;
        mDB.execSQL(statement);
        ValuesBucket values = new ValuesBucket();
        values.putString("sender", "FTS5");
        values.putString("title", "Overview of FTS5");
        values.putString("body", "FTS5 is an SQLite virtual table module that provides full-text " +
                "search functionality to database applications. In their most elementary form, full-text search " +
                "engines allow the user to efficiently search a large collection of documents for the subset that " +
                "contain one or more instances of a search term.");
        mDB.insert("email", null, values);
        values = new ValuesBucket();
        values.putString("sender", "FTS51");
        values.putString("title", "Overview of FTS5");
        values.putString("body", "FTS5 is an SQLite virtual table module that provides full-text " +
                "search functionality to database applications. In their most elementary form, full-text search " +
                "engines allow the user to efficiently search a large collection of documents for the subset that " +
                "contain one or more instances of a search term.");
        mDB.insert("email", null, values);
    }

    private void createIndexTable() {
        mDB.execSQL("DROP TABLE IF EXISTS friends");
        String statement = "CREATE TABLE friends(id,name,city)" ;
        mDB.execSQL(statement);

        ValuesBucket values = new ValuesBucket();
        values.putString("name", "Matt");
        values.putString("city", "san");
        mDB.insert("friends", null, values);
        values = new ValuesBucket();
        values.putString("name", "Dave");
        values.putString("city", "okaLand");
        mDB.insert("friends", null, values);
        values = new ValuesBucket();
        values.putString("name", "Andrew");
        values.putString("city", "burg");
        mDB.insert("friends", null, values);
        values = new ValuesBucket();
        values.putString("name", "todd");
        values.putString("city", "chicago");
        mDB.insert("friends", null, values);
        values = new ValuesBucket();
        values.putString("name", "Blake");
        values.putString("city", "Atlanta");
        mDB.insert("friends", null, values);
        values = new ValuesBucket();
        values.putString("name", "Evan");
        values.putString("city", "Detroit");
        mDB.insert("friends", null, values);
        values = new ValuesBucket();
        values.putString("name", "Nick");
        values.putString("city", "new york");
        mDB.insert("friends", null, values);
        values = new ValuesBucket();
        values.putString("name", "Zack");
        values.putString("city", "seattle");
        mDB.insert("friends", null, values);
    }

    private void testGeneratedColumn() {
        ResultSet resultSet = mDB.query("SELECT * FROM Generated", null);
        if (resultSet != null) {
            HiLog.debug(LABEL, "************************");
            while (resultSet.goToNextRow()) {
                HiLog.debug(LABEL, "entry id: " + resultSet.getString(0));
                HiLog.debug(LABEL, "Name: " + resultSet.getString(1));
                HiLog.debug(LABEL, "Age: " + resultSet.getInt(2));
                HiLog.debug(LABEL, "generated: " + resultSet.getString(3));
                HiLog.debug(LABEL, "----------------------------");
            }
            HiLog.debug(LABEL, "************************");
        }
        resultSet.close();
    }

    private void testFTS5() {
        ResultSet resultSet = mDB.query("SELECT * FROM email WHERE email MATCH 'fts5'");
        if (resultSet != null) {
            HiLog.debug(LABEL, "************************");
            while (resultSet.goToNextRow()) {
                HiLog.debug(LABEL, "matched text: " + resultSet.getString(2));
                HiLog.debug(LABEL, "------------------------");
            }
            HiLog.debug(LABEL, "************************");
        }
        resultSet.close();
    }

    private void textIndex() {
        long ns = Time.getRealActiveTimeNs();
        ResultSet resultSet = mDB.query("SELECT * FROM friends WHERE name='Zack'");
        ns = Time.getRealActiveTimeNs() - ns;
        HiLog.debug(LABEL, "************************");
        HiLog.debug(LABEL, "Time before indexing : " + ns);

        String statement = "CREATE INDEX name_index ON friends(name ASC)";
        mDB.execSQL(statement);

        ns = Time.getRealActiveTimeNs();
        resultSet = mDB.query("SELECT * FROM friends WHERE name='Zack'");
        ns = Time.getRealActiveTimeNs() - ns;
        HiLog.debug(LABEL, "Time after indexing : " + ns);

        if (resultSet != null) {
            while (resultSet.goToNextRow()) {
                HiLog.debug(LABEL, "name : " + resultSet.getString(1));
            }
        }
        HiLog.debug(LABEL, "************************");
        resultSet.close();

        mDB.execSQL("DROP INDEX name_index");
    }
}
